/****** Object:  Table [dbo].[Tag]    Script Date: 10/16/2007 18:41:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Tag](
	[TagId] [int] IDENTITY(1,1) NOT NULL,
	[Tag] [varchar](75) NULL,
	[TagAuthority] [uniqueidentifier] NULL,
	[LockAuthority] [bit] NULL CONSTRAINT [DF_Tag_LockAuthority]  DEFAULT ((0)),
	[EditDate] [datetime] NULL CONSTRAINT [DF_Tag_EditDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_Tag] PRIMARY KEY CLUSTERED 
(
	[TagId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

GO


GO
/****** Object:  Table [dbo].[TagMap]    Script Date: 10/16/2007 18:41:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TagMap](
	[TagId] [int] NOT NULL,
	[ObjectId] [uniqueidentifier] NOT NULL,
	[TaggedBy] [varchar](75) NOT NULL,
	[TaggedDate] [datetime] NOT NULL CONSTRAINT [DF_Table_1_TaggedOn]  DEFAULT (getdate()),
 CONSTRAINT [PK_TagMap_1] PRIMARY KEY CLUSTERED 
(
	[TagId] ASC,
	[ObjectId] ASC,
	[TaggedBy] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[TagMap]  WITH CHECK ADD  CONSTRAINT [FK_TagMap_Tag] FOREIGN KEY([TagId])
REFERENCES [dbo].[Tag] ([TagId])
GO
ALTER TABLE [dbo].[TagMap] CHECK CONSTRAINT [FK_TagMap_Tag]



GO
/****** Object:  StoredProcedure [dbo].[TagAddNew]    Script Date: 10/16/2007 18:42:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		DavidV
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[TagAddNew]
    @Tag VARCHAR(70),
    @Identifier UNIQUEIDENTIFIER,
    @User VARCHAR(100)
AS 
    BEGIN
-- EXEC TagGetTopTags @TagCount = 2000
-- SELECT * FROM Tag JOIN TagMap ON Tag.TagId = TagMap.TagId WHERE TaggedBy <> 'MisesBot'
--delete FROM tagmap WHERE TaggedBy  = 'MisesBot'
--delete FROM [Tag] WHERE tagId NOT IN (SELECT tagid FROM tagmap)

IF LEN(@Tag) < 2
RETURN

SET @Tag = LTRIM(RTRIM(@Tag))
SET @Tag = REPLACE(@Tag,'Econmics','Economics')

/*
IF @Tag = 'mises'
SET @Tag = 'Ludwig von Mises'

IF @Tag = 'Rothbard' OR @Tag ='Murray N.  Rothbard' OR @Tag ='Murray Rothbard'
SET @Tag = 'Murray N. Rothbard'



IF @TAG LIKE '%@%' OR @Tag LIKE '%PDF%' OR @Tag LIKE '%Acrobat %' OR @Tag LIKE '%Palm Download%' OR @Tag ='audio'
 OR @Tag  LIKE '%Distiller%' OR @Tag = 'mises.org' OR @Tag ='and th'
OR @Tag ='Speech' OR @Tag = 'Unknown' OR @Tag LIKE '%mises institute%' OR @Tag = 'PrecisionScan' OR @Tag LIKE '%.qxd%'
RETURN

-- Check for cursewords
IF EXISTS (SELECT * FROM MisesCommunity..cs_Censorship WHERE @Tag LIKE '%' + word + '%')
RETURN
*/
-- Get Tag Id
        DECLARE @TagId INT

        SET @TagId = ( SELECT   TagId
                       FROM     Tag
                       WHERE    Tag = @Tag
                     )

-- Check for duplicates
IF EXISTS (SELECT * FROM TagMap WHERE TagId = @TagId AND ObjectId = @Identifier AND TaggedBy = @User)
RETURN


-- Add New Tag if Missing
        IF @TagId IS NULL 
            BEGIN
                INSERT  INTO Tag ( Tag )
                VALUES  ( @Tag )

                SET @TagID = SCOPE_IDENTITY()
            END

-- Insert tag mapping
        INSERT  INTO TagMap
                (
                  TagId,
                  ObjectId,
                  TaggedBy
                )
        VALUES  (
                  @TagID,
                  @Identifier,
                  @User 
                )

    END




GO
/****** Object:  StoredProcedure [dbo].[TagDelete]    Script Date: 10/16/2007 18:42:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		DavidV
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[TagDelete]
    @tag VARCHAR(75) = NULL,
    @user VARCHAR(100) = NULL,
    @Identifier UNIQUEIDENTIFIER = NULL	
AS 
    BEGIN

        IF @Identifier IS NOT NULL 
            BEGIN

                IF @tag IS NOT NULL 
                    DELETE  FROM TagMap
                    FROM    TagMap
                            INNER JOIN Tag ON TagMap.TagId = Tag.TagId
                    WHERE   ( Tag.Tag = @Tag )
                            AND [TagMap].[ObjectId] = @Identifier
				ELSE IF @user IS NOT NULL
                    DELETE  FROM TagMap
                    FROM    TagMap
                            INNER JOIN Tag ON TagMap.TagId = Tag.TagId
                    WHERE   ( Tag.Tag = @Tag )
                            AND [TagMap].[ObjectId] = @Identifier
							AND TagMap.TaggedBy = @user
                ELSE 
                    DELETE  FROM TagMap
                    FROM    TagMap
                    WHERE   [TagMap].[ObjectId] = @Identifier

            END
        ELSE 
            IF @tag IS NOT NULL 
                DELETE  FROM TagMap
                FROM    TagMap
                        INNER JOIN Tag ON TagMap.TagId = Tag.TagId
                WHERE   ( Tag.Tag = @Tag )

            ELSE 
                DELETE  FROM TagMap
                WHERE   ( TaggedBy = @user )


    END

